package dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import util.DataBaseConnection;

import bean.Empleado;

import dao.Entidad;

public class MySQLEmpleadoDAO implements Entidad<Empleado> {

	@Override
	public Empleado insert(Empleado t) {
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="INSERT INTO persona(nomb_pers,apel_pate,apel_mate,dni,fech_naci,";
			sql = sql + "naci,dire,celu,tele,id_pais,id_depa,id_prov,id_dist) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, t.getNomb_pers());
			ps.setString(2, t.getApel_pate());
			ps.setString(3, t.getApel_mate());
			ps.setInt(4, t.getDni());
			ps.setObject(5, t.getFech_naci());
			ps.setString(6, t.getNaci());	
			ps.setString(7, t.getDire());
			ps.setInt(8, t.getCelu());
			ps.setInt(9, t.getTele());
			ps.setInt(10, t.getId_pais());
			ps.setInt(11, t.getId_depa());
			ps.setInt(12, t.getId_prov());
			ps.setInt(13, t.getId_dist());
			ps.executeUpdate();
			ResultSet rs=ps.getGeneratedKeys();
			if(rs.next()){
				int id=rs.getInt(1);
				t.setId_pers(id);
			}
			sql="INSERT INTO empleado(id_pers,rol,area,suel) VALUES (?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_pers());
			ps.setString(2, t.getRol());
			ps.setString(3, t.getArea());
			ps.setDouble(4, t.getSuel());
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return t;
	}

	@Override
	public int update(Empleado t) {
		Connection con=null;
		int rows = -1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="UPDATE persona SET nomb_pers=?,apel_pate=?,apel_mate=?,";
			sql = sql +	"dni=?,fech_naci=?,naci=?,dire=?,celu=?,tele=?";
			sql = sql +	",id_pais=?,id_depa= ?, id_prov=?, id_dist=? WHERE id_pers = ?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, t.getNomb_pers());
			ps.setString(2, t.getApel_pate());
			ps.setString(3, t.getApel_mate());
			ps.setInt(4, t.getDni());
			ps.setObject(5, t.getFech_naci());
			ps.setString(6, t.getNaci());	
			ps.setString(7, t.getDire());
			ps.setInt(8, t.getCelu());
			ps.setInt(9, t.getTele());
			ps.setInt(10, t.getId_pais());
			ps.setInt(11, t.getId_depa());
			ps.setInt(12, t.getId_prov());
			ps.setInt(13, t.getId_dist());
			ps.setInt(14, t.getId_pers());
			rows =ps.executeUpdate();
			
			sql="UPDATE empleado SET rol=?,area=?,sueldo=? WHERE id_empl=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, t.getRol());
			ps.setString(2, t.getArea());
			ps.setDouble(3, t.getSuel());
			ps.setInt(4, t.getId_pers());
			rows =ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;
	}

	@Override
	public int delete(Empleado t) {
		Connection con=null;
		int rows=-1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="UPDATE persona set esta='N' WHERE id_persona=?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_pers());
			rows=ps.executeUpdate();
			System.out.println(rows);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;
	}

	@Override
	public Empleado findById(Empleado t) {
		/*Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="SELECT * FROM persona p, empleado e WHERE p.id_persona=? and p.estado='R'";
			sql=" and p.id_persona = e.id_empleado";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_persona());
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				t.setId_persona(rs.getInt("id_persona"));
				t.setNombre(rs.getString("nombre"));
				t.setApellido_paterno(rs.getString("apellido_paterno"));
				t.setApellido_materno(rs.getString("apellido_materno"));
				t.setDireccion(rs.getString("direccion"));
				t.setCelular(rs.getInt("celular"));		
				t.setTelefono(rs.getInt("telefono"));
				t.setNacionalidad(rs.getString("nacionalidad"));
				t.setId_pais(rs.getInt("id_pais"));
				t.setId_departamento(rs.getInt("id_departamento"));
				t.setId_provincia(rs.getInt("id_provincia"));
				t.setId_distrito(rs.getInt("id_distrito"));
				t.setFecha_nacimiento(rs.getDate("fecha_nacimiento"));
				t.setDni(rs.getInt("dni"));
				t.setId_empleado(rs.getInt("id_empleado"));
				t.setRol(rs.getString("rol"));
				t.setArea(rs.getString("area"));
				t.setSueldo(rs.getDouble("sueldo"));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}*/
		return null;
		
	}

	@Override
	public List<Empleado> listAll() {
		List<Empleado> empleados = new ArrayList<Empleado>();
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="Select * from persona p, empleado e where p.esta='R' and p.id_pers = e.id_empl";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				Empleado t = new Empleado();
				t.setId_pers(rs.getInt("id_pers"));
				t.setNomb_pers(rs.getString("nomb_pers"));
				t.setApel_pate(rs.getString("apel_pate"));
				t.setApel_mate(rs.getString("apel_mate"));
				t.setDni(rs.getInt("dni"));
				t.setFech_naci(rs.getDate("fech_naci"));
				t.setNaci(rs.getString("naci"));
				t.setDire(rs.getString("dire"));
				t.setCelu(rs.getInt("celu"));		
				t.setTele(rs.getInt("tele"));
				t.setId_pais(rs.getInt("id_pais"));
				t.setId_depa(rs.getInt("id_depa"));
				t.setId_prov(rs.getInt("id_prov"));
				t.setId_dist(rs.getInt("id_dist"));
				t.setRol(rs.getString("rol"));
				t.setArea(rs.getString("area"));
				t.setSuel(rs.getDouble("suel"));
				empleados.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return empleados;
	}

	@Override
	public List<Empleado> listAllCustom(Object... objects) {
		// TODO Auto-generated method stub
		return null;
	}

}
